﻿
using Dapper;
using System.Data;
using System.Threading.Tasks;
using System.Linq;
using System.Collections.Generic;
using AppSocketEntity.GPS;

namespace AppSocketData.GpsInfo
{

    public class GpsInfoData : IGpsInfoData
    {
        private readonly ISqlCommand sqlCommand;
        private readonly string cSql = @"SELECT 
  Imei,
  Phone,
  DeviceInfo,
  DeviceInfoNew,
  GpsTime,
  SysTime,
  HeartTime,
  ServerTime,
  Lng,
  Lat,
  Course,
  Speed,
  Status,
  Acc,
  AccSeconds,
  Location,
  VehicleId,
  VehicleNo,
  CreateDateTime 
FROM
 gps_info ";

        #region 构造函数
        public GpsInfoData(ISqlCommand command)
        {
            this.sqlCommand = command;
        }
        #endregion

        #region InsertAsync
        /// <summary>
        /// 添加新车辆当前位置信息纪录
        /// </summary>
        /// <param name="GpsInfoEntity"></param>
        /// <returns></returns>
        public async Task<GpsInfoEntity> InsertAsync(GpsInfoEntity GpsInfoEntity)
        {
            string sql = sqlCommand.GetInsertSql("gps_info", new[]{ 
			 "Imei",
             "Phone",
             "DeviceInfo",
             "DeviceInfoNew",
             "GpsTime",
             "SysTime",
             "HeartTime",
             "ServerTime",
             "Lng",
             "Lat",
             "Course",
             "Speed",
             "Status",
             "Acc",
             "AccSeconds",
             "Location",
             "VehicleId",
             "VehicleNo", 

            });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Imei", GpsInfoEntity.Imei);
            parameters.Add("?Phone", GpsInfoEntity.Phone);
            parameters.Add("?DeviceInfo", GpsInfoEntity.DeviceInfo);
            parameters.Add("?DeviceInfoNew", GpsInfoEntity.DeviceInfoNew);
            parameters.Add("?GpsTime", GpsInfoEntity.GpsTime);
            parameters.Add("?SysTime", GpsInfoEntity.SysTime);
            parameters.Add("?HeartTime", GpsInfoEntity.HeartTime);
            parameters.Add("?ServerTime", GpsInfoEntity.ServerTime);
            parameters.Add("?Lng", GpsInfoEntity.Lng);
            parameters.Add("?Lat", GpsInfoEntity.Lat);
            parameters.Add("?Course", GpsInfoEntity.Course);
            parameters.Add("?Speed", GpsInfoEntity.Speed);
            parameters.Add("?Status", GpsInfoEntity.Status);
            parameters.Add("?Acc", GpsInfoEntity.Acc);
            parameters.Add("?AccSeconds", GpsInfoEntity.AccSeconds);
            parameters.Add("?Location", GpsInfoEntity.Location);
            parameters.Add("?VehicleId", GpsInfoEntity.VehicleId);
            parameters.Add("?VehicleNo", GpsInfoEntity.VehicleNo); 

            sql = string.Format("{0} {1} WHERE Imei=?Imei;", sql, cSql);

            return await sqlCommand.QueryEntityAsync<GpsInfoEntity>(sql, CommandType.Text, parameters);
        }
        #endregion

        #region updateAsync
        /// <summary>
        /// 修改车辆当前位置信息记录
        /// </summary>
        /// <param name="GpsInfoEntity"></param>
        /// <returns></returns>
        public async Task<GpsInfoEntity> UpdateAsync(GpsInfoEntity GpsInfoEntity)
        {
            string sql = sqlCommand.GetUpdateSql("gps_info", new[]{
                "Phone",
             "DeviceInfo",
             "DeviceInfoNew",
             "GpsTime",
             "SysTime",
             "HeartTime",
             "ServerTime",
             "Lng",
             "Lat",
             "Course",
             "Speed",
             "Status",
             "Acc",
             "AccSeconds",
             "Location",
             "VehicleId",
             "VehicleNo", 

            }, new[] { "Imei" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Imei", GpsInfoEntity.Imei);
            parameters.Add("?Phone", GpsInfoEntity.Phone);
            parameters.Add("?DeviceInfo", GpsInfoEntity.DeviceInfo);
            parameters.Add("?DeviceInfoNew", GpsInfoEntity.DeviceInfoNew);
            parameters.Add("?GpsTime", GpsInfoEntity.GpsTime);
            parameters.Add("?SysTime", GpsInfoEntity.SysTime);
            parameters.Add("?HeartTime", GpsInfoEntity.HeartTime);
            parameters.Add("?ServerTime", GpsInfoEntity.ServerTime);
            parameters.Add("?Lng", GpsInfoEntity.Lng);
            parameters.Add("?Lat", GpsInfoEntity.Lat);
            parameters.Add("?Course", GpsInfoEntity.Course);
            parameters.Add("?Speed", GpsInfoEntity.Speed);
            parameters.Add("?Status", GpsInfoEntity.Status);
            parameters.Add("?Acc", GpsInfoEntity.Acc);
            parameters.Add("?AccSeconds", GpsInfoEntity.AccSeconds);
            parameters.Add("?Location", GpsInfoEntity.Location);
            parameters.Add("?VehicleId", GpsInfoEntity.VehicleId);
            parameters.Add("?VehicleNo", GpsInfoEntity.VehicleNo); 

            sql = string.Format("{0} {1} WHERE Imei = ?Imei;", sql, cSql);

            return await sqlCommand.QueryEntityAsync<GpsInfoEntity>(sql, CommandType.Text, parameters);

        }
        #endregion 

        #region Delete
        /// <summary>
        /// 删除车辆当前位置信息记录
        /// </summary>
        /// <param name="imei"></param>
        /// <returns></returns>
        public async Task<int> DeleteAsync(string  imei)
        {
            string fsql = "DELETE FROM gps_info WHERE Imei = ?Imei";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Imei", imei, DbType.Int64);
            return await sqlCommand.ExecuteAsync(fsql, CommandType.Text, parameters);
        }
        #endregion

        #region GetById
        /// <summary>
        /// 获取车辆当前位置信息记录
        /// </summary>
        /// <param name="imei"></param>
        /// <returns></returns>
        public async Task<GpsInfoEntity> GetByImeiAsync(string  imei)
        {
            string fSql = " WHERE  Imei = ?Imei";

            fSql = cSql + fSql;
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Imei", imei, DbType.Int64);
            return await sqlCommand.QueryEntityAsync<GpsInfoEntity>(fSql, CommandType.Text, parameters);
        }
        #endregion

        #region Exist  设备GPS信息是否存在 
        public bool Exist(string imei)
        {
            string fSql = "SELECT 1 FROM gps_info WHERE  Imei = ?Imei";

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Imei", imei, DbType.String);
            object result = sqlCommand.ExecuteScalar(fSql, CommandType.Text, parameters);
            return result != null;
        }
        #endregion

        #region GetListAsync 查询车辆当前位置信息记录
        public async Task<(int, IEnumerable<GpsInfoEntity>)> GetListAsync(int pageNo, int pageSize)
        {

            DynamicParameters parameters = new DynamicParameters();
            //查询条件
            string fSqlWhere = "";


            //统计
            string fSqlCount = "SELECT COUNT(1) FROM gps_info " + fSqlWhere;

            //排序和分页
            string fSql = cSql + fSqlWhere + "ORDER BY Imei   Limit ?RowBegin ,?RowEnd ;";
            parameters.Add("?RowBegin", (pageNo - 1) * pageSize, DbType.Int64);
            parameters.Add("?RowEnd", pageSize, DbType.Int64);


            //获取数据
            var fRet = await sqlCommand.QueryMultipleAsync<GpsInfoEntity, int>(fSql + fSqlCount, CommandType.Text, parameters);

            var fDataList = fRet.Item1;
            int fCount = fRet.Item2;

            return (fCount, fDataList);
        }
        #endregion

    } 

}
